CREATE   PROCEDURE [dbo].[asi_GetShipPrice]
    
@originKey uniqueidentifier,
@shipMethodName nvarchar(100),
@postalCode nvarchar(20),
@postalCodeFragmentLength int,
@countryCode nvarchar(50),
@shipType int,
@shipPriceValue decimal(18,4)

AS

-- The ship zone search order is:
-- 1. An exact match on the postal code in the ship zone
-- 2. A ship zone whose range encompasses the first
--    postalCodeFragmentLength characters of the postal code
-- 3. A ship zone that has no begin/end target range, i.e.,
--    a catchall zone.
--
-- Any results above are sorted descending on ShipPrice.MinAmount,
-- which would return the price associated with the highest MinAmount
-- that is still less than shipPriceValue
--
-- -1 is returned if no rows match the above queries.
declare @postalCodeFragment nvarchar(20)
set @postalCodeFragment = substring(@postalCode, 1, @postalCodeFragmentLength)

SELECT
    COALESCE
    (
    (SELECT
        TOP 1 sp.Price
        FROM
            ShipZone sz INNER JOIN ShipPrice sp
              ON sz.ShipZoneKey = sp.ShipZoneKey
            INNER JOIN ShipMethod sm
              ON sp.ShipMethodKey = sm.ShipMethodKey
        WHERE
            sz.ShipOriginKey = @originKey
            AND sm.Name = @shipMethodName
            AND (sz.TargetRangeBegin = @postalCode
                 AND sz.TargetRangeEnd = @postalCode)
            AND sp.PricingTypeCode = @shipType
            AND sp.MinAmount <= @shipPriceValue
            AND sz.CountryCode = @countryCode
        ORDER BY
            sp.MinAmount DESC
    ),
    (SELECT
        TOP 1 sp.Price
        FROM
            ShipZone sz INNER JOIN ShipPrice sp
              ON sz.ShipZoneKey = sp.ShipZoneKey
            INNER JOIN ShipMethod sm
              ON sp.ShipMethodKey = sm.ShipMethodKey
        WHERE
            sz.ShipOriginKey = @originKey
            AND sm.Name = @shipMethodName
            AND (sz.TargetRangeBegin <= @postalCodeFragment
                 AND sz.TargetRangeEnd >= @postalCodeFragment)
            AND sp.PricingTypeCode = @shipType
            AND sp.MinAmount <= @shipPriceValue
            AND sz.CountryCode = @countryCode
        ORDER BY
            sp.MinAmount DESC
    ),
    (SELECT
        TOP 1 sp.Price
        FROM
            ShipZone sz INNER JOIN ShipPrice sp
              ON sz.ShipZoneKey = sp.ShipZoneKey
            INNER JOIN ShipMethod sm
              ON sp.ShipMethodKey = sm.ShipMethodKey
        WHERE
            sz.ShipOriginKey = @originKey
            AND sm.Name = @shipMethodName
            AND (sz.TargetRangeBegin = ''
                 AND sz.TargetRangeEnd = '')
            AND sp.PricingTypeCode = @shipType
            AND sp.MinAmount <= @shipPriceValue
            AND sz.CountryCode = @countryCode
        ORDER BY
            sp.MinAmount DESC
    ),
    -1)

GO